#include "SqlTool.h"

QString SqlTool::SQL_INSERT_ROW(QString strTableName, QVariantList varListRowVal)
{
    QString strSql = "insert into " + strTableName + " values(";

    foreach(QVariant var,varListRowVal){
        strSql += "'" + var.toString() + "',";
    }
    strSql.chop(1); //删除最后一个字符
    strSql += ")";

    return strSql;
}

QString SqlTool::SQL_DELETE(QString strTableName, QString strColName, QString strVal)
{
    return QString("delete from %1 where %2='%3'").arg(strTableName,strColName,strVal);
}

QString SqlTool::SQL_UPDATE_ROW(QString strTableName,QStringList strListColName, QVariantList varListColVal)
{
    //"update "+strTableName+" set password =?,authority =? where username =?";
    QString strSqlUpdateRow = "update "+strTableName+" set ";
    for(int i=1;i<strListColName.count();i++){
        strSqlUpdateRow += strListColName[i] + "='" + varListColVal[i].toString() + "',";
    }
    strSqlUpdateRow.chop(1); //删除最后一个字符
    strSqlUpdateRow += "where " + strListColName.first() + "='" + varListColVal.first().toString() + "'";
    return 	strSqlUpdateRow;
}

QString SqlTool::SQL_UPDATE_COLUMN(QString strTableName, QString strIndexColName,QString strIndexVal,
                                   QString strColName,QString strVal)
{
    return QString("update %1 set %2='%3' where %4='%5'")
            .arg(strTableName,strColName,strVal,strIndexColName,strIndexVal);
}

QString SqlTool::SQL_QUERY(QString strTableName, QString strColName, QString strVal)
{
    return QString("select * from %1 where %2='%3'").arg(strTableName,strColName,strVal);
}

QString SqlTool::SQL_QUERY_ALL(QString strTableName)
{
    return QString("select * from %1").arg(strTableName);
}

QString SqlTool::SQL_CREATE_TABLE(QString strTableName, QStringList strListColName)
{
    //默认第一列为主键
    QString strSql = QString("create table %1 (%2 varchar(50) primary key not null,")
            .arg(strTableName,strListColName.first());

    //其他列默认Text类型
    for(int i = 1; i < strListColName.count(); i++){
        strSql += strListColName[i] + " text null,";
    }

    strSql.chop(1);
    strSql += ")";
    return strSql;
}

QString SqlTool::SQL_DROP_TABLE(QString strTableName)
{
    return QString("drop table %1").arg(strTableName);
}

QString SqlTool::SQL_EXIST_TABLE(QString strTableName)
{
    return QString("select * from sqlite_master where type='table' and name='%1'").arg(strTableName);
}
